package com.sinosoft.cnmuseum.servlets;


import com.sinosoft.cnmuseum.dao.*;
import com.sinosoft.cnmuseum.dao.i.IBaseDao;
import com.sinosoft.cnmuseum.entity.*;
import com.sinosoft.cnmuseum.servlets.i.IServlet;
import com.sinosoft.cnmuseum.utils.LogUtils;
import com.sinosoft.cnmuseum.utils.PageUtil;
import com.sinosoft.cnmuseum.utils.Status;
import com.sinosoft.cnmuseum.utils.StringUtils;
import com.sinosoft.commontools.annotated.TransAnnotation;
import com.sinosoft.commontools.annotated.rquestMethod;
import com.sinosoft.commontools.annotated.rquestMethod.ReqMode;
import com.sinosoft.commontools.record.Record;
import org.apache.log4j.Logger;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * Title: MbXtglBaseTableServlet
 * Description: 基表管理
 *
 * @author WEWE
 * @version 1.0
 * @date 2019年1月4日 下午5:15:20
 */
public class MbXtglBaseTableServlet implements IServlet {
    private static Logger logger = Logger.getLogger(MbXtglBaseTableServlet.class);

    @Override
    @rquestMethod(MethodNote = ReqMode.All)
    public Record getData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            String name = data.getStrValue(MbGyByfl.NAME);
            String code = data.getStrValue(MbGyByfl.CODE);
            String description = data.getStrValue(MbGyByfl.DESCRIPTION);

            String countsql = "select count(id) as count from " + tableName + " where 1=1 " + " and  scbz= 0";
            String sql = "select * from " + tableName + " where 1=1 " + " and scbz= 0";
            String ordersql = " order by id asc ";
            String limitsql = " limit ";

            if (StringUtils.isNoEmpty(name)) {
                countsql += " and name like '%" + name + "%' ";
                sql += " and name like '%" + name + "%' ";
            }
            if (StringUtils.isNoEmpty(code)) {
                countsql += " and code like '%" + code + "%' ";
                sql += " and code like '%" + code + "%' ";
            }
            if (StringUtils.isNoEmpty(description)) {
                countsql += " and description like '%" + description + "%' ";
                sql += " and description like '%" + description + "%' ";
            }
            // 这里是获取分页参数，可以前端自己定义
            int pagenumber = data.getIntValue(Status.PAGENUMBER);// 获取当前页
            int pagesize = data.getIntValue(Status.PAGESIZE);// 获取每页多少条数据
            List<Record> countlist = getDao(tableName).getBySql(countsql);
            int total = countlist.get(0).getIntValue("count");
            PageUtil page = new PageUtil(pagenumber, pagesize, total);
            limitsql += page.getStart() + "," + pagesize;
            List<Record> list = getDao(tableName).getBySql(sql + ordersql + limitsql);
            // 组装数据
            result.add(Status.ROWS, list);
            result.add(Status.TOTAL, total);
            result.add(Status.PAGENUMBER, pagenumber);
            result.add(Status.PAGESIZE, pagesize);
            result.add(Status.PAGETOTAL, page.getPagetotal());
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getPullData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            String name = data.getStrValue(MbGyByfl.NAME);
            String code = data.getStrValue(MbGyByfl.CODE);
            String description = data.getStrValue(MbGyByfl.DESCRIPTION);
            String sql = "select * from " + tableName + " where 1=1 " + " and scbz= 0";
            String ordersql = " order by id asc ";
            if (StringUtils.isNoEmpty(name)) {
                sql += " and name like '%" + name + "%' ";
            }
            if (StringUtils.isNoEmpty(code)) {
                sql += " and code like '%" + code + "%' ";
            }
            if (StringUtils.isNoEmpty(description)) {
                sql += " and description like '%" + description + "%' ";
            }
            List<Record> list = getDao(tableName).getBySql(sql + ordersql);
            // 组装数据
            result.add(Status.ROWS, list);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getPullDataByParent(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            int parentid = data.getIntValue("parentid");

            String name = data.getStrValue(MbGyByfl.NAME);
            String code = data.getStrValue(MbGyByfl.CODE);
            String description = data.getStrValue(MbGyByfl.DESCRIPTION);
            String sql = "select * from " + tableName + " where parentid = " + parentid + " and scbz= 0";
            String ordersql = " order by id asc ";
            if (StringUtils.isNoEmpty(name)) {
                sql += " and name like '%" + name + "%' ";
            }
            if (StringUtils.isNoEmpty(code)) {
                sql += " and code like '%" + code + "%' ";
            }
            if (StringUtils.isNoEmpty(description)) {
                sql += " and description like '%" + description + "%' ";
            }
            List<Record> list = getDao(tableName).getBySql(sql + ordersql);
            // 组装数据
            result.add(Status.ROWS, list);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getParentDataByid(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            int id = data.getIntValue("id");

            List<Record> reslist = new ArrayList<>();
            //reslist.add(id);
            getParentById(getDao(tableName), tableName, id, reslist);
            Collections.reverse(reslist);
            // 组装数据
            result.add(Status.ROWS, reslist);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }

    private void getParentById(IBaseDao dao, String tablename, int id, List<Record> reslist) {
        String sql = "select parentid,id,name from " + tablename + " where id = " + id + " ";
        List<Record> list = dao.getBySql(sql);
        if (list.size() > 0) {
            Record record = list.get(0);
            int parentid = record.getIntValue("parentid");
            reslist.add(record);
            if (parentid != 0) {
                getParentById(dao, tablename, parentid, reslist);
            }
        }
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getOneDetail(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            Record gydata = getDao(tableName).getById(data.getIntValue("id"));
            String parentid = gydata.getStrValue("parentid");
            if (parentid != null) {
                int int1 = Integer.parseInt(parentid);
                Record record = getDao(tableName).getById(int1);
                if (record == null) {
                    gydata.add("parentname", null);
                } else {
                    String parentname = record.getStrValue("name");
                    gydata.add("parentname", parentname);
                }
            }
            if (gydata != null) {
                // 组装数据
                result.add(Status.ROWS, gydata);
                // 返回接口调用成功标识
                result.add(Status.SATUS, Status.OK);
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }

    public String getNameById(String tn, int id) {
        String name = "";
        try {
            Record gydata = getDao(tn).getById(id);
            if (gydata != null)
                name = gydata.getStrValue("name");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return name;
    }

    @Override
    @rquestMethod(MethodNote = ReqMode.All)
    @TransAnnotation(isTrans = true)
    public Record insert(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
        //是否删除标志 删除1 不删除0
        int scbz = 0;
        data.add("scbz", scbz);
        String sql = "select * from "+tableName+" where 1=1 "+" and  scbz= 0";
        String ordersql = " order by sorted desc ";
		int sorted=0;
		List<Record> list = getDao(tableName).getBySql(sql+ordersql);
		if(list.isEmpty()) {
			sorted=1;
			data.add("sorted", sorted);
		}else {
			String sort = list.get(0).getStrValue("sorted");
			int parseInt = Integer.parseInt(sort);
			sorted=parseInt+1;
			data.add("sorted", sorted);
		}
            if (getDao(tableName).insert(data)) {

                LogUtils logUtils = new LogUtils();
                String desc = logUtils.getTableName(tableName) + LogUtils.MbXtglBaseTable_INSERT_DESC;
                logUtils.addLog(String.valueOf(LogUtils.type_insert), LogUtils.model_jb, contributor, desc);

                result.add(Status.SATUS, Status.OK);
            } else {
                throw new Exception("插入信息失败！");
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            // 事务回滚，事务模式下，此标识将使此次事务回滚
            result.add(Status.SATUS, Status.FAILED);
        }
        return result;
    }

    @Override
    @rquestMethod(MethodNote = ReqMode.All)
    @TransAnnotation(isTrans = true)
    public Record update(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            Record gydata = getDao(tableName).getById(data.getIntValue("id"));
            if (gydata != null) {
                String name = data.getStrValue(MbGyByfl.NAME);
                String code = data.getStrValue(MbGyByfl.CODE);
                String description = data.getStrValue(MbGyByfl.DESCRIPTION);
                String sorted = data.getStrValue(MbGyByfl.SORTED);
                if (StringUtils.isNoEmpty(name)) {
                    gydata.add(MbGyByfl.NAME, name);
                }
                if (StringUtils.isNoEmpty(code)) {
                    gydata.add(MbGyByfl.CODE, code);
                }
                if (StringUtils.isNoEmpty(description)) {
                    gydata.add(MbGyByfl.DESCRIPTION, description);
                }
                if (StringUtils.isNoEmpty(sorted)) {
                    gydata.add(MbGyByfl.SORTED, sorted);
                }
                if (getDao(tableName).update(gydata)) {

                    LogUtils logUtils = new LogUtils();
                    String desc = logUtils.getTableName(tableName) + LogUtils.MbXtglBaseTable_UPDATE_DESC;
                    logUtils.addLog(String.valueOf(LogUtils.type_update), LogUtils.model_jb, contributor, desc);

                    result.add(Status.SATUS, Status.OK);
                } else {
                    throw new Exception("更新信息失败！");
                }
            } else {
                throw new Exception("更新信息失败！");
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            // 事务回滚，事务模式下，此标识将使此次事务回滚
            result.add(Status.SATUS, Status.FAILED);
        }
        return result;
    }

    @Override
    @rquestMethod(MethodNote = ReqMode.All)
    @TransAnnotation(isTrans = true)
    public Record delete(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            Record gydata = getDao(tableName).getById(data.getIntValue("id"));
            int scbz = 1;
            if (gydata != null) {
                gydata.add("scbz", scbz);
                if (getDao(tableName).update(gydata)) {

                    LogUtils logUtils = new LogUtils();
                    String desc = logUtils.getTableName(tableName) + LogUtils.MbXtglBaseTable_DELETE_DESC;
                    logUtils.addLog(String.valueOf(LogUtils.type_delete), LogUtils.model_jb, contributor, desc);

                    result.add(Status.SATUS, Status.OK);
                } else {
                    throw new Exception("删除信息失败！");
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            // 事务回滚，事务模式下，此标识将使此次事务回滚
            result.add(Status.SATUS, Status.FAILED);
        }
        return result;
    }

    public String getAllChildrenId4Select(int zdbh, String tablename) {
        List<Record> result = new ArrayList<Record>();
        getChildren(zdbh, result, tablename);
        String r = zdbh + ",";
        for (int i = 0; i < result.size(); i++) {
            r += result.get(i).getIntValue("id") + ",";
        }
        if (r.length() > 0)
            r = r.substring(0, r.length() - 1);
        return r;
    }

    public void getChildren(int zdbh, List result, String tablename) {
        String sql = "select id from " + tablename + " where parentid=" + zdbh + " order by id asc";
        try {
            List<Record> list = getDao(tablename).getBySql(sql);
            if (list.size() > 0) {
                result.addAll(list);
                for (int i = 0; i < list.size(); i++) {
                    zdbh = list.get(i).getIntValue("id");
                    getChildren(zdbh, result, tablename);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private IBaseDao getDao(String tableName) throws Exception {
        logger.info("tableName : " + tableName);
        if (MbGyHsfl.MB_GY_HSFL_TABLE_NAME.equals(tableName)) {
            return new MbGyHsflDao();
        }
        if (MbGyByfl.MB_GY_BYFL_TABLE_NAME.equals(tableName)) {
            return new MbGyByflDao();
        }
        if (MbGyMjfl.MB_GY_MJFL_TABLE_NAME.equals(tableName)) {
            return new MbGyMjflDao();
        }
        if (MbGyYqfl.MB_GY_YQFL_TABLE_NAME.equals(tableName)) {
            return new MbGyYqflDao();
        }
        if (MbGyYqzl.MB_GY_YQZL_TABLE_NAME.equals(tableName)) {
            return new MbGyYqzlDao();
        }
        if (MbGyYy.MB_GY_YY_TABLE_NAME.equals(tableName)) {
            return new MbGyYyDao();
        }
        if (MbGyZq.MB_GY_ZQ_TABLE_NAME.equals(tableName)) {
            return new MbGyZqDao();
        }
        if (MbGyCz.MB_GY_CZ_TABLE_NAME.equals(tableName)) {
            return new MbGyCzDao();
        }
        if (MbGyPsjd.MB_GY_PSJD_TABLE_NAME.equals(tableName)) {
            return new MbGyPsjdDao();
        }
        if (MbGyJljzlx.MB_GY_JLJZLX_TABLE_NAME.equals(tableName)) {
            return new MbGyJljzlxDao();
        }
        if (MbGyWjgs.MB_GY_WJGS_TABLE_NAME.equals(tableName)) {
            return new MbGyWjgsDao();
        }
        if (MbGySxcjgs.MB_GY_SXCJGS_TABLE_NAME.equals(tableName)) {
            return new MbGySxcjgsDao();
        }
        if (MbGyLyfs.MB_GY_LYFS_TABLE_NAME.equals(tableName)) {
            return new MbGyLyfsDao();
        }
        if (MbGyFsjz.MB_GY_FSJZ_TABLE_NAME.equals(tableName)) {
            return new MbGyFsjzDao();
        }
        if (MbGyBblx.MB_GY_BBLX_TABLE_NAME.equals(tableName)) {
            return new MbGyBblxDao();
        }
        if (MbGyDq.MB_GY_DQ_TABLE_NAME.equals(tableName)) {
            return new MbGyDqDao();
        }
        if (MbGySyysjq.MB_GY_SYYSJQ_TABLE_NAME.equals(tableName)) {
            return new MbGySyysjqDao();
        }
        if (MbGyZyqxlx.MB_GY_ZYQXLX_TABLE_NAME.equals(tableName)) {
            return new MbGyZyqxlxDao();
        }
        if (MbGyNd.MB_GY_ND_TABLE_NAME.equals(tableName)) {
            return new MbGyNdDao();
        }
        if (MbGyCyyz.MB_GY_CYYZ_TABLE_NAME.equals(tableName)) {
            return new MbGyCyyzDao();
        }
        if (MbXtCzlx.MB_XT_CZLX_TABLE_NAME.equals(tableName)) {
            return new MbXtCzlxDao();
        }
        if (MbGyGj.MB_GY_GJ_TABLE_NAME.equals(tableName)) {
            return new MbGyGjDao();
        }
        if (MbGyZjlx.MB_GY_ZJLX_TABLE_NAME.equals(tableName)) {
            return new MbGyZjlxDao();
        }
        if (MbGyTc.MB_GY_TC_TABLE_NAME.equals(tableName)) {
            return new MbGyZjlxDao();
        }
        throw new Exception("this tableName cannot find its dao : " + tableName);
    }
    //**********************************************特殊基表年代***************************************************************

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getNdData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;

            String countsql = "select count(id) as count from " + tableName + " where 1=1 " + " and  scbz= 0";
            ;
            String sql = "select * from " + tableName + " where 1=1 " + " and  scbz= 0";
            ;
            String ordersql = " order by id asc";
            String limitsql = " limit ";

            String country = data.getStrValue(MbGyNd.COUNTRY);
            String dynasty = data.getStrValue(MbGyNd.DYNASTY);
            String years = data.getStrValue(MbGyNd.YEARS);
            String startyear = data.getStrValue(MbGyNd.STARTYEAR);
            String endyear = data.getStrValue(MbGyNd.ENDYEAR);
            String emperor = data.getStrValue(MbGyNd.EMPEROR);
            String remark = data.getStrValue(MbGyNd.REMARK);


            if (StringUtils.isNoEmpty(country)) {
                countsql += " and country like '%" + country + "%' ";
                sql += " and country like '%" + country + "%' ";
            }
            if (StringUtils.isNoEmpty(dynasty)) {
                countsql += " and dynasty like '%" + dynasty + "%' ";
                sql += " and dynasty like '%" + dynasty + "%' ";
            }
            if (StringUtils.isNoEmpty(years)) {
                countsql += " and years like '%" + years + "%' ";
                sql += " and years like '%" + years + "%' ";
            }
            if (StringUtils.isNoEmpty(startyear)) {
                countsql += " and startyear = '" + startyear + "' ";
                sql += " and startyear = '" + startyear + "' ";
            }
            if (StringUtils.isNoEmpty(endyear)) {
                countsql += " and endyear = '" + endyear + "' ";
                sql += " and endyear = '" + endyear + "' ";
            }
            if (StringUtils.isNoEmpty(emperor)) {
                countsql += " and emperor like '%" + emperor + "%' ";
                sql += " and emperor like '%" + emperor + "%' ";
            }
            if (StringUtils.isNoEmpty(remark)) {
                countsql += " and remark like '%" + remark + "%' ";
                sql += " and remark like '%" + remark + "%' ";
            }

            // 这里是获取分页参数，可以前端自己定义
            int pagenumber = data.getIntValue(Status.PAGENUMBER);// 获取当前页
            int pagesize = data.getIntValue(Status.PAGESIZE);// 获取每页多少条数据
            List<Record> countlist = getDao(tableName).getBySql(countsql);
            int total = countlist.get(0).getIntValue("count");
            PageUtil page = new PageUtil(pagenumber, pagesize, total);
            limitsql += page.getStart() + "," + pagesize;
            List<Record> list = getDao(tableName).getBySql(sql + ordersql + limitsql);
            // 组装数据
            result.add(Status.ROWS, list);
            result.add(Status.TOTAL, total);
            result.add(Status.PAGENUMBER, pagenumber);
            result.add(Status.PAGESIZE, pagesize);
            result.add(Status.PAGETOTAL, page.getPagetotal());
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            // 返回接口调用失败标识
            result.add(Status.SATUS, Status.FAILED);
            // 返回接口调用失败信息，需要自己添加
            result.add(Status.MSG, "查询失败！");
        }
        // 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getPullNdData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;

            String sql = "select * from " + tableName + " where 1=1 " + " and  scbz= 0";
            ;
            String ordersql = " order by id asc ";

            String country = data.getStrValue(MbGyNd.COUNTRY);
            String dynasty = data.getStrValue(MbGyNd.DYNASTY);
            String years = data.getStrValue(MbGyNd.YEARS);
            String startyear = data.getStrValue(MbGyNd.STARTYEAR);
            String endyear = data.getStrValue(MbGyNd.ENDYEAR);
            String emperor = data.getStrValue(MbGyNd.EMPEROR);
            String remark = data.getStrValue(MbGyNd.REMARK);

            if (StringUtils.isNoEmpty(country)) {
                sql += " and country like '%" + country + "%' ";
            }
            if (StringUtils.isNoEmpty(dynasty)) {
                sql += " and dynasty like '%" + dynasty + "%' ";
            }
            if (StringUtils.isNoEmpty(years)) {
                sql += " and years like '%" + years + "%' ";
            }
            if (StringUtils.isNoEmpty(startyear)) {
                sql += " and startyear = '" + startyear + "' ";
            }
            if (StringUtils.isNoEmpty(endyear)) {
                sql += " and endyear = '" + endyear + "' ";
            }
            if (StringUtils.isNoEmpty(emperor)) {
                sql += " and emperor like '%" + emperor + "%' ";
            }
            if (StringUtils.isNoEmpty(remark)) {
                sql += " and remark like '%" + remark + "%' ";
            }

            List<Record> list = getDao(tableName).getBySql(sql + ordersql);
            // 组装数据
            result.add(Status.ROWS, list);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            // 返回接口调用失败标识
            result.add(Status.SATUS, Status.FAILED);
            // 返回接口调用失败信息，需要自己添加
            result.add(Status.MSG, "查询失败！");
        }
        // 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getPullNdData4select(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;

            String sql = "select id,startyear,endyear,CONCAT(country,'.',dynasty,"
                    + " case when startyear is not null "
                    + "      then CONCAT('(', "
                    + "                  case when substr(startyear,1,1)='-' "
                    + "                       then  CONCAT('公元前',substr(startyear,2,LENGTH(startyear)))  "
                    + "                       else CONCAT('公元',substr(startyear,1,LENGTH(startyear))) "
                    + "                  end "
                    + "                  ,'-',"
                    + "                  case when substr(endyear,1,1)='-' "
                    + "                       then  CONCAT('公元前',substr(endyear,2,LENGTH(endyear)))  "
                    + "                       else CONCAT('公元',substr(endyear,1,LENGTH(endyear))) "
                    + "                  end "
                    + "                  ,')')"
                    + "   else ''    end"
                    + "  ) description from " + MbGyNd.MB_GY_ND_TABLE_NAME + " where (years is null or years='' ) and scbz=0 order by id";

            List<Record> list = getDao(tableName).getBySql(sql);
            // 组装数据
            result.add(Status.ROWS, list);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            // 返回接口调用失败标识
            result.add(Status.SATUS, Status.FAILED);
            // 返回接口调用失败信息，需要自己添加
            result.add(Status.MSG, "查询失败！");
        }
        // 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public String getNdTextById(String id) {
        Record result = new Record();
        try {
            if (StringUtils.isNullOrEmpty(id)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少id参数");
                return "";
            }
            ;

            String sql = "select id,startyear,endyear,CONCAT(country,'.',dynasty,"
                    + " case when startyear is not null "
                    + "      then CONCAT('(', "
                    + "                  case when substr(startyear,1,1)='-' "
                    + "                       then  CONCAT('公元前',substr(startyear,2,LENGTH(startyear)))  "
                    + "                       else CONCAT('公元',substr(startyear,1,LENGTH(startyear))) "
                    + "                  end "
                    + "                  ,'-',"
                    + "                  case when substr(endyear,1,1)='-' "
                    + "                       then  CONCAT('公元前',substr(endyear,2,LENGTH(endyear)))  "
                    + "                       else CONCAT('公元',substr(endyear,1,LENGTH(endyear))) "
                    + "                  end "
                    + "                  ,')')"
                    + "   else ''    end"
                    + "  ) description from " + MbGyNd.MB_GY_ND_TABLE_NAME + " where id=" + id + " ";

            List<Record> list = getDao(MbGyNd.MB_GY_ND_TABLE_NAME).getBySql(sql);
            String year_text = "";
            if (list != null && list.size() > 0) {
                year_text = list.get(0).getStrValue("description");
            }
            return year_text;
        } catch (Exception e) {
        }
        // 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return "";
    }


    @rquestMethod(MethodNote = ReqMode.All)
    public Record getNdDetailData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            Record nd = getDao(tableName).getById(data.getIntValue("id"));
            if (nd != null) {
                // 组装数据
                result.add(Status.ROWS, nd);
                // 返回接口调用成功标识
                result.add(Status.SATUS, Status.OK);
            }
        } catch (Exception e) {
            // 返回接口调用失败标识
            result.add(Status.SATUS, Status.FAILED);
            // 返回接口调用失败信息，需要自己添加
            result.add(Status.MSG, "查询失败！");
        }
        // 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    @TransAnnotation(isTrans = true)
    public Record insertNd(HttpServletRequest request, HttpServletResponse response, Record data) {
        // TODO Auto-generated method stub
        Record result = new Record();
        try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            //是否删除标志 删除1 不删除0
            int scbz = 0;
            data.add("scbz", scbz);
            if (getDao(tableName).insert(data)) {

                LogUtils logUtils = new LogUtils();
                String desc = logUtils.getTableName(tableName) + LogUtils.MbXtglBaseTable_DELETE_DESC;
                logUtils.addLog(String.valueOf(LogUtils.type_insert), LogUtils.model_jb, contributor, desc);

                result.add(Status.SATUS, Status.OK);
            } else {
                throw new Exception("插入信息失败！");
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            // 事务回滚，事务模式下，此标识将使此次事务回滚
            result.add(Status.SATUS, Status.FAILED);
        }
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    @TransAnnotation(isTrans = true)
    public Record updateNd(HttpServletRequest request, HttpServletResponse response, Record data) {
        // TODO Auto-generated method stub
        Record result = new Record();
        try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            Record nd = getDao(tableName).getById(data.getIntValue("id"));
            if (nd != null) {
                String country = data.getStrValue(MbGyNd.COUNTRY);
                String dynasty = data.getStrValue(MbGyNd.DYNASTY);
                String years = data.getStrValue(MbGyNd.YEARS);
                String startyear = data.getStrValue(MbGyNd.STARTYEAR);
                String endyear = data.getStrValue(MbGyNd.ENDYEAR);
                String emperor = data.getStrValue(MbGyNd.EMPEROR);
                String remark = data.getStrValue(MbGyNd.REMARK);

                if (StringUtils.isNoEmpty(country)) {
                    nd.add(MbGyNd.COUNTRY, country);
                }
                if (StringUtils.isNoEmpty(dynasty)) {
                    nd.add(MbGyNd.DYNASTY, dynasty);
                }
                if (StringUtils.isNoEmpty(years)) {
                    nd.add(MbGyNd.YEARS, years);
                }
                if (StringUtils.isNoEmpty(startyear)) {
                    nd.add(MbGyNd.STARTYEAR, startyear);
                }
                if (StringUtils.isNoEmpty(endyear)) {
                    nd.add(MbGyNd.ENDYEAR, endyear);
                }
                if (StringUtils.isNoEmpty(emperor)) {
                    nd.add(MbGyNd.EMPEROR, emperor);
                }
                if (StringUtils.isNoEmpty(remark)) {
                    nd.add(MbGyNd.REMARK, remark);
                }
                if (getDao(tableName).update(nd)) {

                    LogUtils logUtils = new LogUtils();
                    String desc = logUtils.getTableName(tableName) + LogUtils.MbXtglBaseTable_DELETE_DESC;
                    logUtils.addLog(String.valueOf(LogUtils.type_update), LogUtils.model_jb, contributor, desc);

                    // 事务模式下，此标识将使此次事务提交
                    result.add(Status.SATUS, Status.OK);
                } else {
                    throw new Exception("更新信息失败！");
                }
            } else {
                throw new Exception("更新信息失败！");
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            // 事务回滚，事务模式下，此标识将使此次事务回滚
            result.add(Status.SATUS, Status.FAILED);
        }
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    @TransAnnotation(isTrans = true)
    public Record deleteNd(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            Record nd = getDao(tableName).getById(data.getIntValue("id"));
            int scbz = 1;
            if (nd != null) {
                nd.add("scbz", scbz);
                if (getDao(tableName).update(nd)) {

                    LogUtils logUtils = new LogUtils();
                    String desc = logUtils.getTableName(tableName) + LogUtils.MbXtglBaseTable_DELETE_DESC;
                    logUtils.addLog(String.valueOf(LogUtils.type_delete), LogUtils.model_jb, contributor, desc);

                    result.add(Status.SATUS, Status.OK);
                } else {
                    throw new Exception("删除信息失败！");
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            // 事务回滚，事务模式下，此标识将使此次事务回滚
            result.add(Status.SATUS, Status.FAILED);
        }
        return result;
    }

    //***********************************多层基表管理**************************************************************

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getTreeData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;

            String sql = "select * from " + tableName + " where 1=1 " + " and scbz= 0";
            String ordersql = " order by id asc ";
            List<Record> list = getDao(tableName).getBySql(sql + ordersql);//获取所有有效的基表信息
            List<Record> gylist = new ArrayList<Record>();
            //
            if (list == null || list.size() == 0) {
                result.add(Status.MSG, "查询失败！");
                return result;
            }
            for (Record record : list) {
                String id = record.getStrValue("id");
                String name = record.getStrValue("name");
                String pId = record.getStrValue("parentid");
                Record re = new Record();
                re.add("name", name);
                re.add("id", id);
                re.add("pId", pId);
                gylist.add(re);
            }
            result.add("data", gylist);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            // 返回接口调用失败标识
            result.add(Status.SATUS, Status.FAILED);
            // 返回接口调用失败信息，需要自己添加
            result.add(Status.MSG, "查询失败！");
        }
        // 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getTreeData2(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;

            List<Record> list = getChildrenRecord(0, tableName);
            result.add("rows", list);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            // 返回接口调用失败标识
            result.add(Status.SATUS, Status.FAILED);
            // 返回接口调用失败信息，需要自己添加
            result.add(Status.MSG, "查询失败！");
        }
        // 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return result;
    }


    //获取某级数据并判断有无子分类
    @rquestMethod(MethodNote = ReqMode.All)
    public Record getOneLevelData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            int pid = 0;
            if (data.containsKey("id")) {
                pid = data.getIntValue("id");
            }
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            //获取第一层数据
            List<Record> list = getOneLevelChildrenRecord(pid, tableName);
            for (int i = 0; i < list.size(); i++) {
                List<Record> subList = getOneLevelChildrenRecord(list.get(i).getIntValue("id"), tableName);
                if (subList != null && subList.size() > 0) {
                    list.get(i).add("isParent", true);
//                    list.get(i).add("children", subList);
                } else {
                    list.get(i).add("isParent", false);
                }
                list.get(i).add("pId", pid);
            }
            result.add("rows", list);
            // 返回接口调用成功标识
//            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            // 返回接口调用失败标识
            result.add(Status.SATUS, Status.FAILED);
            // 返回接口调用失败信息，需要自己添加
            result.add(Status.MSG, "查询失败！");
        }
//         接口返回Record对象，框架会自动封装返回给请求result对象中的数据
        return result;
    }


    public List<Record> getChildrenRecord(int id, String tablename) {
        String sql = "select id,name from " + tablename + " where scbz= 0 and  parentid=" + id + " order by id asc";
        try {
            List<Record> list = getDao(tablename).getBySql(sql);
            if (list.size() > 0) {
                for (int i = 0; i < list.size(); i++) {
                    Record childRecord = list.get(i);
                    id = childRecord.getIntValue("id");
                    childRecord.add("children", getChildrenRecord(id, tablename));
                }
                return list;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private List<Record> getOneLevelChildrenRecord(int id, String tablename) {
        String sql = "select id,name from " + tablename + " where scbz= 0 and  parentid=" + id + " order by id asc";
        try {
            List<Record> list = getDao(tablename).getBySql(sql);
            if (list.size() > 0) {
                return list;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    @rquestMethod(MethodNote = ReqMode.All)
    @TransAnnotation(isTrans = true)
    public Record deleteTwoTable(HttpServletRequest request, HttpServletResponse response, Record data) {

        String tableName = data.getStrValue("tn");
        int id = data.getIntValue("id");

        HttpSession session = request.getSession(false);
        String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者
        LogUtils logUtils = new LogUtils();
        String desc = logUtils.getTableName(tableName) + LogUtils.MbXtglBaseTable_DELETE_DESC;
        logUtils.addLog(String.valueOf(LogUtils.type_delete), LogUtils.model_jb, contributor, desc);

        return checkGytableSon(tableName, id);
    }

    private Record checkGytableSon(String tableName, Integer id) {
        Record result = new Record();
        try {
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;


            String sql = "select * from " + tableName + " where 1=1 " + " and  scbz= 0 " + " and parentid='" + id + "' ";
            List<Record> list = getDao(tableName).getBySql(sql);
            if (list.size() > 0 || list != null) {
                for (Record record : list) {
                    checkGytableSon(tableName, record.getIntValue("id"));
                }
            }
            Record gydata = getDao(tableName).getById(id);
            int scbz = 1;
            if (gydata != null) {
                gydata.add("scbz", scbz);
                if (getDao(tableName).update(gydata)) {
                    result.add(Status.SATUS, Status.OK);
                } else {
                    throw new Exception("删除信息失败！");
                }
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            // 事务回滚，事务模式下，此标识将使此次事务回滚
            result.add(Status.SATUS, Status.FAILED);
        }
        return result;

    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getPullTwoData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            List<Record> list = null;
            if (tableName.equals("mb_gy_cz")) {
                String czsql = "SELECT CONCAT_WS('-',b.id,c.id,d.id) AS id ,CONCAT_WS('.',b.name,c.name,d.name) AS name from  " + " (SELECT * FROM " + tableName + " AS a WHERE a.parentid='0' AND a.scbz='0' ) AS b LEFT JOIN " + tableName + " AS c ON  b.id=c.parentid AND c.scbz='0' " + " LEFT JOIN " + tableName + " AS d ON  c.id=d.parentid AND d.scbz='0' ";
                String czordersql = " order by id ";
                list = getDao(tableName).getBySql(czsql + czordersql);
            } else if (tableName.equals("mb_gy_yy")) {
                String czsql = "SELECT CONCAT_WS('-',b.id,c.id,d.id) AS id ,CONCAT_WS('.',b.name,c.name,d.name) AS name from  " + " (SELECT * FROM " + tableName + " AS a WHERE a.parentid='0' AND a.scbz='0' ) AS b LEFT JOIN " + tableName + " AS c ON  b.id=c.parentid AND c.scbz='0' " + " LEFT JOIN " + tableName + " AS d ON  c.id=d.parentid AND d.scbz='0' ";
                String czordersql = " order by id ";
                list = getDao(tableName).getBySql(czsql + czordersql);
            } else {
                String sql = "SELECT CONCAT_WS('-',b.id,c.id) AS id ,CONCAT_WS('.',b.name,c.name) AS name from " + " (SELECT * FROM " + tableName + " AS a WHERE a.parentid='0' AND a.scbz='0' ) AS b  LEFT JOIN " + tableName + " AS c ON  b.id=c.parentid AND c.scbz='0' ";
                String ordersql = " order by id ";
                list = getDao(tableName).getBySql(sql + ordersql);
            }
            if (list != null) {
                // 组装数据
                result.add(Status.ROWS, list);
                // 返回接口调用成功标识
                result.add(Status.SATUS, Status.OK);
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getSonData(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {

            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            String parentid = data.getStrValue("parentid");

            String countsql = "select count(id) as count from " + tableName + " where 1=1 " + " and  scbz= 0" + " and parentid='" + parentid + "'";
            String sql = "select * from " + tableName + " where 1=1 " + " and scbz= 0" + " and parentid='" + parentid + "'";
            String ordersql = " order by id asc ";
            String limitsql = " limit ";

            // 这里是获取分页参数，可以前端自己定义
            int pagenumber = data.getIntValue(Status.PAGENUMBER);// 获取当前页
            int pagesize = data.getIntValue(Status.PAGESIZE);// 获取每页多少条数据
            List<Record> countlist = getDao(tableName).getBySql(countsql);
            int total = countlist.get(0).getIntValue("count");
            PageUtil page = new PageUtil(pagenumber, pagesize, total);
            limitsql += page.getStart() + "," + pagesize;
            List<Record> list = getDao(tableName).getBySql(sql + ordersql + limitsql);
            // 组装数据
            result.add(Status.ROWS, list);
            result.add(Status.TOTAL, total);
            result.add(Status.PAGENUMBER, pagenumber);
            result.add(Status.PAGESIZE, pagesize);
            result.add(Status.PAGETOTAL, page.getPagetotal());
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }

    @rquestMethod(MethodNote = ReqMode.All)
    public Record getPeersDataForZtree(HttpServletRequest request, HttpServletResponse response, Record data) {
        Record result = new Record();
        try {
            String tableName = data.getStrValue("tn");
            if (StringUtils.isNullOrEmpty(tableName)) {
                result.add(Status.SATUS, Status.FAILED);
                result.add(Status.MSG, "缺少tn参数");
                return result;
            }
            ;
            String name = data.getStrValue(MbGyByfl.NAME);
            String code = data.getStrValue(MbGyByfl.CODE);
            String description = data.getStrValue(MbGyByfl.DESCRIPTION);
            String sql = "select * from " + tableName + " where 1=1 " + " and scbz= 0";
            String ordersql = " order by id asc ";
            if (StringUtils.isNoEmpty(name)) {
                sql += " and name like '%" + name + "%' ";
            }
            if (StringUtils.isNoEmpty(code)) {
                sql += " and code like '%" + code + "%' ";
            }
            if (StringUtils.isNoEmpty(description)) {
                sql += " and description like '%" + description + "%' ";
            }
            List<Record> list = getDao(tableName).getBySql(sql + ordersql);
            // 组装数据
            for (Record record : list) {
                record.add("pId", 0);
                record.add("isParent", false);
            }
            result.add(Status.ROWS, list);
            // 返回接口调用成功标识
            result.add(Status.SATUS, Status.OK);
        } catch (Exception e) {
            logger.error(e.getMessage());
            result.add(Status.SATUS, Status.FAILED);
            result.add(Status.MSG, "查询失败！");
        }
        return result;
    }
}
